﻿/*
 * Notifier
 * By Joseph Styons
 * Evaluates outstanding notifications and sends messages that have been
 * configured on the front-end
 * 
 * Expected usage is this utility is run as a scheduled windows job at regular intervals
 * Alternate usage would be to leave it running permanently, and put 
 * 
 * TODO: read connection string and email account info from the database
//*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace notifier
{
    class Notification
    {
        //this class corresponds to the results of the sproc "USP_GETACTIVENOTIFICATIONLIST"
        public Guid _NotificationID;
        public Guid _NotificationScheduleID;
        public Notification(Guid NotificationID, Guid NotificationScheduleID)
        {
            _NotificationID = NotificationID;
            _NotificationScheduleID = NotificationScheduleID;
        }
    }

    class NotificationMessage
    {
        //this class corresponds to the results of the sproc "USP_NOTIFICATIONRECIPIENTS"
        public string _EmailAddress;
        public string _MessageSubject;
        public string _MessageBody;
        public NotificationMessage(string EmailAddress, string MessageSubject, string MessageBody)
        {
            _EmailAddress = EmailAddress;
            _MessageBody = MessageBody;
            _MessageSubject = MessageSubject;
        }
    }

    class Notifier
    {
        const string c_ConnectionString = "Data Source=tcp:sql2k805.discountasp.net;Initial Catalog=SQL2008R2_886711_sep;User ID=SQL2008R2_886711_sep_user;Password=$3P1s;";

        static void Main(string[] args)
        {
            SendNotifications();
        }

        private static void SendNotifications()
        {
            //see what notifications are active
            //for each notification, see if the condition has been met
            //for each recipient, send an alert
            foreach (Notification n in GetActiveNotifications())
            {
                foreach (NotificationMessage msg in GetNotificationRecipients(n))
                {
                    SendNotification(msg);
                }
            }
        }

        private static List<Notification> GetActiveNotifications()
        {            
            List<Notification> result = new List<Notification>();
            using (SqlConnection conn = new SqlConnection(c_ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new System.Data.SqlClient.SqlCommand("USP_GETACTIVENOTIFICATIONLIST", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (SqlDataReader activenotifications = cmd.ExecuteReader())
                    {
                        while (activenotifications.Read())
                        {
                            result.Add(new Notification(new Guid(activenotifications["NOTIFICATIONID"].ToString()),new Guid(activenotifications["NOTIFICATIONSCHEDULEID"].ToString())));
                        }  //while
                    }
                }
            }
            return result;
        }

        private static List<NotificationMessage> GetNotificationRecipients(Notification notification)
        {
            List<NotificationMessage> result = new List<NotificationMessage>();
            using (SqlConnection conn = new SqlConnection(c_ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new System.Data.SqlClient.SqlCommand("USP_NOTIFICATIONRECIPIENTS", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@NOTIFICATIONID", notification._NotificationID.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@NOTIFICATIONSCHEDULEID", notification._NotificationScheduleID.ToString()));
                    using (SqlDataReader recipients = cmd.ExecuteReader())
                    {
                        while (recipients.Read())
                        {
                            result.Add(new NotificationMessage(recipients["EMAILADDRESS"].ToString(),recipients["MESSAGE_SUBJECT"].ToString(),recipients["MESSAGE_BODY"].ToString()));
                        }
                    }
                }
            }
            return result;
        }

        private static void SendNotification(NotificationMessage msg)
        {
            Communications.EmailSender email = new Communications.EmailSender();            
            email.EmailServerName = "smtp.gmail.com";
            email.FromEmailAddress = "jstyons@gmail.com";
            email.FromEmailAccountPassword = "testpassword";
            email.FromEmailAccountUsername = "jstyons@gmail.com";
            email.MessageSubject = msg._MessageSubject;
            email.MessageBody = msg._MessageBody;
            email.RecipientEmailAddresses.Add(msg._EmailAddress);
            email.SendEmail();
        }
    }
}